﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using Model;

namespace FirebirdTool
{
    public static class Util
    {
        public static string CnnStr = "Database={0};ServerType=0;User=SYSDBA;Password=masterkey";
        public static string DB = null;
        public static string ProjectPath = null;
        public static string Namespace = null;
        public static int NewTableIndex = 0;
        public static int NewSQLIndex = 0;
        //======================================================================

        public static List<DetailDB> GetDetailDB()
        {
            int id = 0;
            int parentID = 0;

            List<DetailDB> lstDB = new List<DetailDB>();
            lstDB.Add(new DetailDB(0, 0, -1, "Tables", "", 0));

            using (FbConnection cnn = new FbConnection(string.Format(CnnStr, DB)))
            {
                cnn.Open();

                string scmd = "SELECT rdb$relation_name as TBNAME, rdb$description as TBDESC FROM rdb$relations WHERE rdb$view_blr IS NULL AND (rdb$system_flag IS NULL OR rdb$system_flag = 0) ORDER BY rdb$relation_name";

                using (FbCommand cmdTable = new FbCommand(scmd, cnn))
                {
                    using (FbDataReader readerTable = cmdTable.ExecuteReader())
                    {
                        List<Column> lstColumn = null;
                        Column col = null;
                        int i = 0;
                        int tableParentID = 0;
                        string fullColName = "";

                        while (readerTable.Read())
                        {
                            id++;
                            tableParentID = id;

                            DetailDB db = new DetailDB();
                            db.DBType = 1;
                            db.ImgIndex = 0;
                            db.ID = id;
                            db.ParentID = parentID;
                            db.RealName = ((string)readerTable["TBNAME"]).Trim();

                            if (readerTable["TBDESC"] is DBNull)
                                db.Name = db.RealName;
                            else
                                db.Name = ((string)readerTable["TBDESC"]).Trim();

                            lstDB.Add(db);

                            lstColumn = GetListColumn(db.RealName);
                            for (i = 0; i < lstColumn.Count; i++)
                            {
                                id++;
                                col = lstColumn[i];

                                fullColName = col.Name + " (" + col.DataType + ")";

                                DetailDB dbCol = new DetailDB();
                                dbCol.DBType = 2;
                                dbCol.ImgIndex = (col.PK ? 1 : 2);
                                dbCol.ID = id;
                                dbCol.ParentID = tableParentID;
                                dbCol.Name = fullColName;
                                dbCol.RealName = col.RealName;
                                lstDB.Add(dbCol);
                            }
                        }
                    }
                }

                id++;
                parentID = id;
                lstDB.Add(new DetailDB(3, id, -1, "Stored Procedures", "", 3));
                scmd = "SELECT rdb$Procedure_name AS SPNAME,rdb$description as SPDESC FROM rdb$procedures WHERE rdb$system_flag IS NULL OR rdb$system_flag = 0 ORDER BY rdb$Procedure_name";
                using (FbCommand cmdSP = new FbCommand(scmd, cnn))
                {
                    using (FbDataReader readerSP = cmdSP.ExecuteReader())
                    {
                        while (readerSP.Read())
                        {
                            id++;
                            DetailDB db = new DetailDB();
                            db.DBType = 4;
                            db.ImgIndex = 3;
                            db.ID = id;
                            db.ParentID = parentID;
                            db.RealName = ((string)readerSP["SPNAME"]).Trim();

                            if (readerSP["SPDESC"] is DBNull)
                                db.Name = db.RealName;
                            else
                                db.Name = ((string)readerSP["SPDESC"]).Trim();

                            lstDB.Add(db);
                        }
                    }
                }
            }

            return lstDB;
        }

        public static List<Column> GetListColumn(string tableName)
        {
            List<Column> lstColumn = new List<Column>();

            using (FbConnection cnn = new FbConnection(string.Format(CnnStr, DB)))
            {
                cnn.Open();

                string scmd = "SELECT RDB$FIELD_NAME AS COLNAME FROM rdb$index_segments WHERE RDB$INDEX_NAME = (SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE rdb$relation_name = '" + tableName + "' AND RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') ORDER BY RDB$FIELD_POSITION;";
                string PKCol = null;

                using (FbCommand cmdPK = new FbCommand(scmd, cnn))
                {
                    using (FbDataReader readerPK = cmdPK.ExecuteReader())
                    {
                        if (readerPK.Read())
                            PKCol = ((string)readerPK["COLNAME"]).Trim();
                    }
                }

                scmd = "SELECT f.rdb$character_set_id AS ISCHAR,rf.rdb$field_id AS ID,rf.rdb$field_name AS COLNAME,rf.rdb$field_position AS POS,rf.RDB$DESCRIPTION AS DESCRIP,rf.RDB$NULL_FLAG AS ISNULL,f.RDB$CHARACTER_LENGTH AS LEN,f.RDB$FIELD_TYPE AS DATATYPE FROM rdb$fields f JOIN rdb$relation_fields rf ON rf.rdb$field_source = f.rdb$field_name WHERE rf.rdb$relation_name = '" + tableName + "' ORDER BY rf.rdb$field_position;";
                using (FbCommand cmdTable = new FbCommand(scmd, cnn))
                {
                    using (FbDataReader readerTable = cmdTable.ExecuteReader())
                    {
                        while (readerTable.Read())
                        {
                            Column column = new Column(readerTable);

                            if (PKCol != null)
                                if (column.RealName == PKCol)
                                    column.PK = true;

                            lstColumn.Add(column);
                        }
                    }
                }
            }

            return lstColumn;
        }

        public static string GetSPSourceCode(string SPName)
        {
            string s = "";

            using (FbConnection cnn = new FbConnection(string.Format(CnnStr, DB)))
            {
                cnn.Open();

                string scmd = "SELECT RDB$DESCRIPTION, RDB$PROCEDURE_SOURCE AS SPSOURCE FROM RDB$PROCEDURES WHERE RDB$PROCEDURE_NAME='" + SPName + "';";

                using (FbCommand cmd = new FbCommand(scmd, cnn))
                {
                    using (FbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            if ((reader["SPSOURCE"] is DBNull) == false)
                                s = ((string)reader["SPSOURCE"]).Trim();
                    }
                }

                scmd = "SELECT a.RDB$PARAMETER_NAME AS PNAME, a.RDB$PARAMETER_TYPE AS PTYPE, a.RDB$FIELD_SOURCE AS PSOURCE,b.RDB$FIELD_TYPE AS DATATYPE, b.RDB$CHARACTER_LENGTH AS LEN, b.RDB$CHARACTER_SET_ID AS ISCHAR FROM RDB$PROCEDURE_PARAMETERS a JOIN RDB$FIELDS b ON a.RDB$FIELD_SOURCE = b.RDB$FIELD_NAME WHERE RDB$PROCEDURE_NAME = '" + SPName + "' ORDER BY RDB$PARAMETER_TYPE,RDB$PARAMETER_NUMBER";
                string input = "";
                string output = "";
                string name = "";
                string dataType = "";

                using (FbCommand cmdParam = new FbCommand(scmd, cnn))
                {
                    using (FbDataReader readerParam = cmdParam.ExecuteReader())
                    {
                        while (readerParam.Read())
                        {
                            if ((readerParam["PNAME"] is DBNull) == false)
                                name = ((string)readerParam["PNAME"]).Trim();

                            switch (Convert.ToInt32(readerParam["DATATYPE"]))
                            {
                                case 8:
                                    dataType = "Integer";
                                    break;
                                case 37:
                                    dataType = "Varchar(" + readerParam["LEN"].ToString() + ") CHARACTER SET UNICODE_FSS";
                                    break;
                                case 7:
                                    dataType = "Smallint";
                                    break;
                                case 14:
                                    dataType = "Char(" + readerParam["LEN"].ToString() + ")";
                                    break;
                                case 16:
                                    dataType = "Bigint";
                                    break;
                                case 10:
                                    dataType = "Float";
                                    break;
                                case 35:
                                    dataType = "TimeStamp";
                                    break;
                                case 261:
                                    dataType = "Blob Sub_type Binary";

                                    if (!(readerParam["ISCHAR"] is DBNull))
                                        if (readerParam["ISCHAR"] != null)
                                            if (Convert.ToInt32(readerParam["ISCHAR"]) == 0)
                                                dataType = "Blob Sub_type Text";
                                    break;
                            }

                            if (Convert.ToInt32(readerParam["PTYPE"]) == 0)
                                input += "\r\n\t" + name + " " + dataType + ",";
                            else
                                output += "\r\n\t" + name + " " + dataType + ",";
                        }
                    }
                }

                if (input.EndsWith(","))
                    input = input.Substring(0, input.Length - 1);
                if (output.EndsWith(","))
                    output = output.Substring(0, output.Length - 1);

                string preS = "ALTER PROCEDURE " + SPName;
                if (input != "")
                    preS += "(" + input + ")\r\n";
                else
                    preS += "\r\n";
                if (output != "")
                    preS += "RETURNS (" + output + ")\r\n";

                preS += "AS\r\n";
                s = preS + s;
            }

            return s;
        }

        //======================================================================
        public static FbDataReader ExecReader(string scmd)
        {
            if (string.IsNullOrEmpty(scmd))
                return null;

            FbConnection cnn = new FbConnection(string.Format(CnnStr, DB));
            cnn.Open();

            FbCommand cmd = new FbCommand(scmd, cnn);
            return cmd.ExecuteReader();
        }

        public static string ExecNonQuery(string scmd)
        {
            if (string.IsNullOrEmpty(scmd))
                return null;

            try
            {
                using (FbConnection cnn = new FbConnection(string.Format(CnnStr, DB)))
                {
                    cnn.Open();

                    using (FbCommand cmd = new FbCommand(scmd, cnn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return null;
        }

        //======================================================================
        public static bool GetBool(object value)
        {
            if (value == null)
                return false;
            if (value is DBNull)
                return false;
            return (bool)value;
        }

        public static int GetInt(object value)
        {
            if (value == null)
                return 0;
            if (value is DBNull)
                return 0;
            return (int)value;
        }

        public static int GetInt(object value, int valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            if (value is DBNull)
                return valueWhenNull;
            return (int)value;
        }

        public static void SaveSetting(string appPath, string _projectPath, string _namespace, string _fbFile)
        {
            string[] arr = new string[] { _projectPath, _namespace, _fbFile };

            if (appPath.EndsWith("\\") == false)
                appPath += "\\";
            appPath += "config.rms";
            System.IO.File.WriteAllLines(appPath, arr, Encoding.UTF8);
        }
    }
}
